package DBLayer;
import ModelLayer.Address;
import java.sql.*;
import java.util.ArrayList;

public class DBAddress {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBAddress() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Address getAddressByID(int id) {
        return selectSingle("SELECT `id_address`, `id_country`, `id_state`, `id_customer`, `id_manufacturer`, `id_supplier`, `alias`, `company`, `lastname`, `firstname`, `address1`, `address2`, `postcode`, `city`, `other`, `phone`, `phone_mobile`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd`, `active`, `deleted` FROM `ps_address` WHERE `id_address` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<Address> getAddressList() {
        return selectMultiple("SELECT `id_address`, `id_country`, `id_state`, `id_customer`, `id_manufacturer`, `id_supplier`, `alias`, `company`, `lastname`, `firstname`, `address1`, `address2`, `postcode`, `city`, `other`, `phone`, `phone_mobile`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd`, `active`, `deleted` FROM `ps_address` ORDER BY `id_address` ASC;");
    }

    private Address selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Address address = new Address();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                address = buildAddress(result);
            }
            System.out.println("Fetching Address Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return address;
    }

    private ArrayList<Address> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Address> addresses = new ArrayList<Address>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Address address = new Address();
                address = buildAddress(result);
                addresses.add(address);
            }
            System.out.println("Fetching Address List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return addresses;
    }

    private Address buildAddress(ResultSet result) {
        Address address = new Address();
        try {
            address.setIDAddress(result.getInt("id_address"));
            address.setIDCountry(result.getInt("id_country"));
            address.setIDState(result.getInt("id_state"));
            address.setIDCustomer(result.getInt("id_customer"));
            address.setIDManufacturer(result.getInt("id_manufacturer"));
            address.setIDSupplier(result.getInt("id_supplier"));
            address.setAlias(result.getString("alias"));
            address.setCompany(result.getString("company"));
            address.setLastname(result.getString("lastname"));
            address.setFirstname(result.getString("firstname"));
            address.setAddress1(result.getString("address1"));
            address.setAddress2(result.getString("address2"));
            address.setPostcode(result.getString("postcode"));
            address.setCity(result.getString("city"));
            address.setOther(result.getString("other"));
            address.setPhone(result.getString("phone"));
            address.setPhoneMobile(result.getString("phone_mobile"));
            address.setDateAdd(result.getInt("date_add"));
            address.setDateUpd(result.getInt("date_upd"));
            if (result.getInt("active") == 1) {
                address.setActive(true);
            } else {
                address.setActive(false);
            }
            if (result.getInt("deleted") == 1) {
                address.setDeleted(true);
            } else {
                address.setDeleted(false);
            }

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return address;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addAddress(Address address) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_address` (`id_address`, `id_country`, `id_state`, `id_customer`, `id_manufacturer`, `id_supplier`, `alias`, `company`, `lastname`, `firstname`, `address1`, `address2`, `postcode`, `city`, `other`, `phone`, `phone_mobile`, `date_add`, `date_upd`, `active`, `deleted`) "
                + "VALUES (" + address.getIDAddress() + ", " + address.getIDCountry() + ", " + address.getIDState() + ", " + address.getIDCustomer() + ", " + address.getIDManufacturer() + ", " + address.getIDSupplier() + ", '" + address.getAlias() + "', '" + address.getCompany() + "', '" + address.getLastname() + "', '" + address.getFirstname() + "', '" + address.getAddress1() + "', '" + address.getAddress2() + "', '" + address.getPostcode() + "', '" + address.getCity() + "', '" + address.getOther() + "', '" + address.getPhone() + "', '" + address.getPhoneMobile() + "', FROM_UNIXTIME(" + address.getDateAdd() + "), FROM_UNIXTIME(" + address.getDateUpd() + "), ";
        if (address.getActive() == true)        { query += "1, "; }
        else                                    { query += "0, "; }
        if (address.getDeleted() == true)       { query += "1);"; }
        else                                    { query += "0);"; }
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Address ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateAddress(Address address, int id) {
        int returnID = 0;
        Statement stmt = null;
	String query = "UPDATE `ps_address` SET "
                + "`id_address`      = "  + address.getIDAddress() + ", "
                + "`id_country`      = "  + address.getIDCountry() + ", "
                + "`id_state`        = "  + address.getIDState() + ", "
                + "`id_customer`     = "  + address.getIDCustomer() + ", "
                + "`id_manufacturer` = "  + address.getIDManufacturer() + ", "
                + "`id_supplier`     = "  + address.getIDSupplier() + ", "
                + "`alias`           = '" + address.getAlias() + "', "
                + "`company`         = '" + address.getCompany() + "', "
                + "`lastname`        = '" + address.getLastname() + "', "
                + "`firstname`       = '" + address.getFirstname() + "', "
                + "`address1`        = '" + address.getAddress1() + "', "
                + "`address2`        = '" + address.getAddress2() + "', "
                + "`postcode`        = '" + address.getPostcode() + "', "
                + "`city`            = '" + address.getCity() + "', "
                + "`other`           = '" + address.getOther() + "', "
                + "`phone`           = '" + address.getPhone() + "', "
                + "`phone_mobile`    = '" + address.getPhoneMobile() + "', "
                + "`date_add`        = FROM_UNIXTIME("  + address.getDateAdd() + "), "
                + "`date_upd`        = FROM_UNIXTIME("  + address.getDateUpd() + "), ";
        if (address.getActive() == true)    { query += "`active` = 1, "; }
        else                                { query += "`active` = 0, "; }
        if (address.getDeleted() == true)   { query += "`deleted` = 1 "; }
        else                                { query += "`deleted` = 0 "; }
	query += "WHERE `id_address` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = address.getIDAddress();
            System.out.println("Updated Address ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteAddress(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_address` WHERE `id_address` = " + id + " LIMIT 1;");
            System.out.println("Deleted Address ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
